package com.sdy.healthcode.biz.mapper;

import com.sdy.healthcode.biz.dto.PersonnelEntryAndExitDTO;
import com.sdy.healthcode.biz.model.ReportPeoplePass;
import com.sdy.mvc.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 人员进出分析报表 Mapper 接口
 * </p>
 *
 * @author cgj
 * @since 2020-02-28
 */
public interface ReportPeoplePassMapper extends BaseMapper<ReportPeoplePass> {


 @Select("select n_in.type, in_num, health_in_num, out_num, health_out_num, \n" +
            "in_num+out_num as sum_num,health_in_num+health_out_num as sum_health_num \n" +
            "from (select type,sum(num) as in_num, sum(if(health_status=1,num,0)) as health_in_num \n" +
            "from user_action_view where action=1 group by type) n_in left join \n" +
            "(select type,sum(num) as out_num, sum(if(health_status=1,num,0)) as health_out_num from user_action_view \n" +
            "where action=2 and health_status=1 group by type) n_out on n_in.type=n_out.type")
    List<PersonnelEntryAndExitDTO> getEtryAndExitBase();

}
